﻿using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VLine.InputDto._0416;
using HCQ2_Model.VLine.OutputDto._0416;
using HCQ2_Model.VRDD.InputDto._0714;
using HCQ2_Model.VRDD.OutputDto._0714;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRGoodDiscountDAL: IT_VRGoodDiscountDAL
    {
        /// <summary>
        ///  获取创始商铺信息
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<FolderOutput> GetFolderOutput(FolderInfoParam param)
        {
            sb?.Clear();
            sb.Append($@"SELECT n.*,l.sale,p1.partner_num,p2.apply_num FROM (SELECT f.*,COUNT(i.file_id)AS num FROM 
	        (SELECT folder_id,folder_name,folder_path,folder_note,folder_url,city FROM dbo.T_DocumentFolder WHERE page_type='DDCircle' ");
            if (param.folder_id > 0)
                sb.Append($"  AND folder_id IN({param.folder_id}) ");//导航树id
            else
                sb.Append($" AND founder_id IN({param.user_id}) ");//商铺创始人
            sb.Append($@")f LEFT JOIN
	            (SELECT file_id,folder_path FROM dbo.T_DocumentFolderRelation)r ON r.folder_path LIKE f.folder_path+'%' LEFT JOIN
	            (SELECT file_id FROM dbo.T_DocumentInfo WHERE file_status=5 AND page_type='DDCircle' AND if_remove=0)i ON r.file_id=i.file_id
	            GROUP BY f.folder_id,f.folder_name,f.folder_path,f.folder_note,f.folder_url,city
            )n LEFT JOIN
            (SELECT FolderId,SUM(gold_num)AS sale FROM dbo.T_VRGoldLog WHERE FolderId>0 AND create_date>=CAST(YEAR(GETDATE()) AS NVARCHAR(5))+'-01-01' AND 
            create_date<=CAST(YEAR(GETDATE()) AS NVARCHAR(5))+'-12-31' GROUP BY FolderId
            )l ON n.folder_id=l.FolderId LEFT JOIN
            (SELECT folder_id,COUNT(0)AS partner_num FROM dbo.T_VRFounderApply WHERE apply_status=1 GROUP BY folder_id)p1 ON n.folder_id=p1.folder_id LEFT JOIN
            (SELECT folder_id,COUNT(0)AS apply_num FROM dbo.T_VRFounderApply WHERE apply_status=0 GROUP BY folder_id)p2 ON n.folder_id=p2.folder_id;");
            return SqlHelper.ExecuteList<FolderOutput>(sb.ToString());
        }
        /// <summary>
        ///  获取合伙商铺
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<ApplyFolder> GetApplyFolder(FolderInfoParam param)
        {
            sb?.Clear();
            sb.Append($@"SELECT a.*,f.folder_name,f.folder_path,f.folder_note,f.folder_url,p.apply_num,l.sale,s.score_num,n.line_num,g.gold_num FROM 
            (SELECT folder_id,user_level,ref_name,CONVERT(varchar(100),apply_date,120) AS apply_date,user_id FROM dbo.T_VRFounderApply 
            WHERE apply_status=1 AND user_id={param.user_id} ");
            if (param.folder_id > 0)
                sb.Append($" AND folder_id={param.folder_id} ");
            sb.Append($@")a INNER JOIN
            (SELECT folder_id,folder_name,folder_path,folder_note,folder_url FROM dbo.T_DocumentFolder WHERE 1=1)f ON a.folder_id=f.folder_id LEFT JOIN
            (SELECT folder_id,COUNT(0)AS apply_num FROM dbo.T_VRFounderApply WHERE apply_status=0 GROUP BY folder_id)p ON a.folder_id=p.folder_id LEFT JOIN
            (SELECT FolderId,SUM(gold_num)AS sale FROM dbo.T_VRGoldLog WHERE FolderId>0 AND create_date>=CAST(YEAR(GETDATE()) AS NVARCHAR(5))+'-01-01' AND 
            create_date<=CAST(YEAR(GETDATE()) AS NVARCHAR(5))+'-12-31' GROUP BY FolderId)l ON a.folder_id=l.FolderId LEFT JOIN
            (SELECT user_id,folder_id,SUM(score_num)AS score_num FROM dbo.T_VRUserIntegralLog GROUP BY user_id,folder_id)s ON a.folder_id=s.folder_id AND 
            a.user_id=s.user_id LEFT JOIN 
            (SELECT ref_id,folder_id,COUNT(0)AS line_num FROM T_VRFounderApply GROUP BY ref_id,folder_id)n ON a.user_id=n.ref_id AND a.folder_id=n.folder_id LEFT JOIN 
            (SELECT FolderId,user_id,SUM(gold_num)AS gold_num FROM dbo.T_VRGoldLog WHERE operator_type=1 AND FolderId>0 GROUP BY FolderId,user_id)g ON a.folder_id=g.FolderId 
            AND a.user_id=g.user_id;");
            return SqlHelper.ExecuteList<ApplyFolder>(sb.ToString());
        }
        /// <summary>
        ///  获取未关联
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<MaterialOutput> GetMaterialOutput(MaterialParam param)
        {
            sb?.Clear();
            sb.Append($@"SELECT info.* FROM (SELECT file_id,file_name,mater_type FROM dbo.T_DocumentInfo i WHERE file_status=5 AND page_type='{param.doc_type}' ");
            if (!string.IsNullOrEmpty(param.mater_type))
                sb.Append($" AND mater_type='{param.mater_type}' ");
            sb.Append($@" AND NOT EXISTS(SELECT dd_id FROM dbo.T_VRDDCircleRelGood d WHERE i.file_id=d.dd_id OR i.file_id=d.rel_id))info 
            INNER JOIN (SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE '{param.folder_path}%')rel ON info.file_id=rel.file_id");
            return SqlHelper.ExecuteList<MaterialOutput>(sb.ToString());
        }
        /// <summary>
        ///  获取和指定商品未关联的素材
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<UnlinkedMaterialOutput> GetUnlinkedMaterial(UnlinkedMaterialParam param)
        {
            sb?.Clear();
            sb.Append($@"SELECT info.* FROM 
            (SELECT file_id FROM dbo.T_DocumentFolderRelation n WHERE folder_path LIKE '{param.folder_path}%' AND 
            NOT EXISTS(SELECT t.dd_id FROM (SELECT dd_id=(CASE WHEN dd_id={param.fileID} THEN rel_id ELSE dd_id END) 
            FROM dbo.T_VRDDCircleRelGood WHERE dd_id={param.fileID} OR rel_id={param.fileID}) t WHERE t.dd_id=n.file_id))rel INNER JOIN 
            (SELECT file_id,file_name FROM dbo.T_DocumentInfo WHERE file_status=5 AND page_type='{param.doc_type}' ");
            if (!string.IsNullOrEmpty(param.mater_type))
                sb.Append($" AND mater_type='{param.mater_type}' ");
            sb.Append($@")info ON info.file_id = rel.file_id");
            return SqlHelper.ExecuteList<UnlinkedMaterialOutput>(sb.ToString());
        }
        /// <summary>
        ///  获取商铺商品折扣信息
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputGoodRate> GetGoodRateList(InputGoodRate param, int user_id)
        {
            sb?.Clear();
            sb.Append($@"select top {param.rows} * from(select file_id,file_order,create_time,update_time,file_name as fileName,isSoldOut,isEntity,pay_num as payNum,store_id as storeID,ROW_NUMBER() OVER(order by ");
            switch (param.priceOrder)
            {
                case 4:sb.Append(" i.create_time desc "); break;
                case 5: sb.Append(" i.file_order desc "); break;
                case 6: sb.Append(" i.file_order asc "); break;
                default: sb.Append(" i.update_time desc "); break;
            }
            sb.Append($@")as num,folderID=(select folder_id from T_DocumentFolderRelation where file_id=i.file_id),
                isHold=(select cast(COUNT(0)as bit) from T_VRFavoriteGoods where user_id=6 and file_id=i.file_id),
                isStar=(select cast(COUNT(0)as bit) from T_DocumentUserStar where create_id=6 and file_id=i.file_id),
                filePrice=(SELECT MIN(ori_price) FROM dbo.T_VRGoodDisiPrice WHERE file_id=i.file_id),
                rate0=ISNULL((SELECT TOP 1 rate FROM dbo.T_VRGoodDiscount WHERE file_id=i.file_id AND user_level=0),0)
                from T_DocumentInfo i where 1=1 ");
            if (param.store_id > 0)
                sb.Append($" and store_id={param.store_id} ");
            if (!string.IsNullOrEmpty(param.folderPath))
                sb.Append($" and store_id in(select folder_id from T_DocumentFolderRelation where folder_path like '{param.folderPath}%') ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" and (file_name like '%{param.keyword}%' or file_note like '%{param.keyword}%') ");
            sb.Append($")d where d.num>{(param.page - 1) * param.rows}; ");
            return SqlHelper.ExecuteList<OutputGoodRate>(sb.ToString());
        }
    }
}